package de.esb.o3c.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import de.esb.o3c.global.Constants;

/**
 */
public class SqlConnector {
	private Logger logger = Logger.getLogger(SqlConnector.class);

	Connection con = null;

	private String url = "jdbc:sqlserver://";
	private String serverName = Constants.SQL_SERVER;
	private String portNumber = Constants.SQL_PORT;
	private String databaseName = Constants.SQL_DBNAME;
	private String userName = Constants.SQL_USER;
	private String password = Constants.SQL_PASSWORD;
	// Informs the driver to use server a side-cursor,
	// which permits more than one active statement
	// on a connection.
	private final String selectMethod = "cursor";

	/**
	 * Constructor for SqlConnector.
	 * 
	 * @param serverName
	 *            String
	 * @param portNumber
	 *            String
	 * @param databaseName
	 *            String
	 * @param userName
	 *            String
	 * @param password
	 *            String
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 */
	public SqlConnector(String serverName, String portNumber,
			String databaseName, String userName, String password) throws ClassNotFoundException, SQLException {
		this.serverName = serverName;
		this.portNumber = portNumber;
		this.databaseName = databaseName;
		this.userName = userName;
		this.password = password;
		logger.debug("SQqlConnector created");
		con = getConnection();
	}

	/**
	 * Method getConnectionUrl.
	 * 
	 * @return String
	 */
	private String getConnectionUrl() {
		return url + serverName + ":" + portNumber + ";databaseName="
				+ databaseName + ";selectMethod=" + selectMethod + ";";
	}

	/**
	 * Method getConnection.
	 * 
	 * @return Connection
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private Connection getConnection() throws ClassNotFoundException,
			SQLException {
		logger.debug("Try to get connection");
		Connection con = null;
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		DriverManager.setLoginTimeout(5);
		con = DriverManager.getConnection(getConnectionUrl(), userName,
				password);
		if (con != null)
			logger.info("Connection Successful!");
		return con;
	}

	/*
	 * Display the driver properties, database details
	 */

	public void displayDbProperties() throws SQLException,
			ClassNotFoundException {
		java.sql.DatabaseMetaData dm = null;
		java.sql.ResultSet rs = null;

		if (con != null) {
			dm = con.getMetaData();
			logger.debug("Driver Information");
			logger.debug("\tDriver Name: " + dm.getDriverName());
			logger.debug("\tDriver Version: " + dm.getDriverVersion());
			logger.debug("\nDatabase Information ");
			logger.debug("\tDatabase Name: " + dm.getDatabaseProductName());
			logger.debug("\tDatabase Version: "
					+ dm.getDatabaseProductVersion());
			logger.debug("Avalilable Catalogs ");
			rs = dm.getCatalogs();
			while (rs.next()) {
				logger.debug("\tcatalog: " + rs.getString(1));
			}
			rs.close();
			rs = null;
		} else
			logger.error("Error: No active Connection");
		dm = null;
	}

	/**
	 * Method executeQuery.
	 * 
	 * @param query
	 *            String
	 * @return ResultSet
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public ResultSet executeQuery(String query) throws SQLException,
			ClassNotFoundException {
		ResultSet rs = null;
		Statement stmt;
		
		if(con!=null){
		con = getConnection();

		stmt = con.createStatement();
		rs = stmt.executeQuery(query);
		}
		else{
			logger.error("Error: No active Connection");
		}
		return rs;
	}

	/**
	 * Method closeConnection.
	 * 
	 * @param con
	 *            Connection
	 */
	public void closeConnection() {
		try {
			if (con != null)
				con.close();
			con = null;
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
		}
	}

}
